package com.situ.edu.dao.impl;

import com.situ.edu.dao.IXiCourseDao;
import com.situ.edu.pojo.entity.Course;
import com.situ.edu.pojo.query.XiQuery;
import com.situ.edu.pojo.vo.CourseCountVO;
import com.situ.edu.pojo.vo.CourseExcelVO;
import com.situ.edu.pojo.vo.CourseVO;
import com.situ.edu.pojo.vo.XiVO;
import com.situ.edu.util.JDBCUtils;
import com.situ.edu.util.JDBCUtils2;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.ObjectUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class XiCourseDaoImpl implements IXiCourseDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public List<XiVO> selectByCourse(XiQuery xiQuery) {
        String sql = "SELECT c.id,c.name,c.gredit,x.`name` AS xiName,t.place,c.create_time,c.update_time,c.xi_id\n" +
                "FROM course AS c LEFT JOIN xi AS x\n" +
                "ON c.xi_id = x.id LEFT JOIN taught AS t\n" +
                "ON c.id=t.course_id where 1=1";
        List<Object> queryList = new ArrayList<>();

        if (!ObjectUtils.isEmpty(xiQuery.getXiId())) {
            sql += " and c.xi_id=?";
            queryList.add(xiQuery.getXiId());
        }

        sql += " limit ?,?";
        int offset = (xiQuery.getPage() - 1)*xiQuery.getLimit();
        queryList.add(offset);
        queryList.add(xiQuery.getLimit());

        return template.query(sql,
                new BeanPropertyRowMapper<>(XiVO.class),queryList.toArray());
    }

    @Override
    public int selectTotalCount(XiQuery xiQuery) {
        String sql = "SELECT count(*)\n" +
                "FROM course AS c LEFT JOIN xi AS x\n" +
                "ON c.xi_id = x.id LEFT JOIN taught AS t\n" +
                "ON c.id=t.course_id where 1=1";
        List<Object> queryList = new ArrayList<>();
        if (!ObjectUtils.isEmpty(xiQuery.getXiId())) {
            sql += " and c.xi_id=?";
            queryList.add(xiQuery.getXiId());
        }

        return template.queryForObject(sql,Integer.class,queryList.toArray());
    }

    @Override
    public void deleteById(int id) {
        template.update("delete from course where id=?",id);
    }

    @Override
    public void add(Course course) {
        template.update("insert into course(name,gredit,xi_id) values (?,?,?)",
                course.getName(),course.getGredit(),course.getXiId());
    }

    @Override
    public Course selectById(int id) {
        return template.queryForObject("select * from course where id=?",
                new BeanPropertyRowMapper<>(Course.class),id);
    }

    @Override
    public void update(Course course) {
        template.update("update course set name=?,gredit=?,xi_id=? where id=?",
                course.getName(),course.getGredit(),course.getXiId(),course.getId());
    }

    @Override
    public List<CourseCountVO> selectCourseCount(){

        String sql = "SELECT `name`,count(*) AS `value`\n" +
                "FROM course AS c INNER JOIN elective AS e\n" +
                "ON c.id=e.course_id GROUP BY c.id";

        return template.query(sql,new BeanPropertyRowMapper<>(CourseCountVO.class));

    }


    @Override
    public List<CourseExcelVO> selectAll() {
        String sql = "SELECT c.id,c.name,c.gredit,x.`name` AS xiName,t.place,c.create_time,c.update_time,c.xi_id\n" +
                "FROM course AS c INNER JOIN xi AS x\n" +
                "ON c.xi_id = x.id INNER JOIN taught AS t\n" +
                "ON c.id=t.course_id;";
        return template.query(sql,new BeanPropertyRowMapper<>(CourseExcelVO.class));
    }

    @Override
    public void addExcel(CourseVO courseVO) {
        template.update("insert into course(name,gredit,xi_id) values (?,?,?)",
                courseVO.getName(),courseVO.getGredit(),courseVO.getXiId());
    }

    @Override
    public List<Course> selectCourseAll() {
        return template.query("select * from course",
                new BeanPropertyRowMapper<>(Course.class));
    }
}
